/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sample;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamWriter;

/**
 *
 * @author truongmh
 */
public class Controller extends HttpServlet {

    static private String strXMLFileProductPath = "xml/Products.xml";
      static private String strXMLFileCategoryPath = "xml/Categories.xml";
    static private int intPreviewChars = 500;
    static private String strConnectionString = "jdbc:mysql://localhost/csvn";
    static private String strQuerySelectProducts = "SELECT * FROM PRODUCT";
     static private String strQuerySelectCategories = "SELECT * FROM CATEGORY";
    static private String strQuerySelectProductsByCategory = "";

    static private String strQueryInsertProduct =
            "INSERT INTO Product ( \n"
            + " , product_id \n"
            + " , product_name \n"
            + " , product_description \n"
            + " , product_category \n"
            + ") VALUES ( \n"
            + " , ? \n"
            + " , ? \n"
            + " , ? \n"
            //+ " GETDATE() \n"
            + ")";

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        request.setCharacterEncoding("UTF-8");
        try {
            HttpSession session = request.getSession();
            String action = request.getParameter("action");
            if (action.equalsIgnoreCase("init")) {
                if (generateXML(request.getRealPath(strXMLFileProductPath), request.getRealPath(strXMLFileCategoryPath))) {
                    session.setAttribute("isXMLReady", true);
                    RequestDispatcher objRd = request.getRequestDispatcher("index.jsp");
                    objRd.forward(request, response);
                }
            } else if (action.equalsIgnoreCase("submit")) {
                String productId = request.getParameter("txtProductId");
                String submitUser = request.getParameter("txtSubmitUser");
                if (insertProduct(Integer.parseInt(productId), submitUser) == 1) {
                     if (generateXML(request.getRealPath(strXMLFileProductPath), request.getRealPath(strXMLFileCategoryPath))) {
                        RequestDispatcher objRd = request.getRequestDispatcher("index.jsp?action=view&id="+productId);
                        objRd.forward(request, response);
                    }
                }
            }
        } finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    private Connection createDBConnection() {
        try {
           Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(strConnectionString,"root","root");
            return con;
        } catch (SQLException ex) {
            Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    private boolean generateXML(String strXMLFilePath, String strXMLFilePath2) {

        //Load data from DB to XML
        Connection objCon = createDBConnection();
        if (objCon == null) {
            //cannot create db connection
            return false;
        } else {
            try {
                //Write to XML
                XMLOutputFactory objXOF = XMLOutputFactory.newInstance();
                FileOutputStream objFOS = new FileOutputStream(new File(strXMLFilePath));

                XMLStreamWriter objXSW = objXOF.createXMLStreamWriter(objFOS, "UTF-8");


                objXSW.writeStartDocument("UTF-8", "1.0");
                objXSW.writeStartElement("root");

                Statement objStm = objCon.createStatement();
                ResultSet objRs = objStm.executeQuery(strQuerySelectProducts);
                objXSW.writeStartElement("product_list");
                while (objRs.next()) {
                    objXSW.writeStartElement("product");

                    objXSW.writeStartElement("id");
                    objXSW.writeCharacters(objRs.getString("product_id"));
                    objXSW.writeEndElement();

                    objXSW.writeStartElement("name");
                    objXSW.writeCharacters(objRs.getString("product_name"));
                    objXSW.writeEndElement();

                      objXSW.writeStartElement("detail");
                    objXSW.writeCharacters(objRs.getString("product_detail"));
                    objXSW.writeEndElement();

                      objXSW.writeStartElement("price");
                    objXSW.writeCharacters(objRs.getString("product_price"));
                    objXSW.writeEndElement();

                     objXSW.writeStartElement("onsaleprice");
                    objXSW.writeCharacters(objRs.getString("product_onsaleprice"));
                    objXSW.writeEndElement();

                      objXSW.writeStartElement("category");
                    objXSW.writeCharacters(objRs.getString("product_category"));
                    objXSW.writeEndElement();

                    objXSW.writeStartElement("image");
                    objXSW.writeCharacters(objRs.getString("product_image"));
                    objXSW.writeEndElement();

                    objXSW.writeEndElement();
                }
                objXSW.writeEndElement();
                
                objRs.close();

                 
                objXSW.writeEndElement();
                objXSW.writeEndDocument();
                objRs.close();
                objFOS = new FileOutputStream(new File(strXMLFilePath2));

                 objXSW = objXOF.createXMLStreamWriter(objFOS, "UTF-8");
                  objXSW.writeStartDocument("UTF-8", "1.0");
                objXSW.writeStartElement("root");
                  objRs = objStm.executeQuery(strQuerySelectCategories);
 objXSW.writeStartElement("category_list");
                while (objRs.next()) {
                    objXSW.writeStartElement("category");

                    objXSW.writeStartElement("id");
                    objXSW.writeCharacters(objRs.getString("category_id"));
                    objXSW.writeEndElement();
                    objXSW.writeStartElement("name");
                    objXSW.writeCharacters(objRs.getString("category_name"));
                    objXSW.writeEndElement();
                    objXSW.writeStartElement("image");
                    objXSW.writeCharacters(objRs.getString("category_image"));
                    objXSW.writeEndElement();
                  objXSW.writeEndElement();
                }
                objRs.close();
                objXSW.writeEndElement();
                objXSW.writeEndDocument();
                objStm.close();
                objCon.close();

                //release writer
                objXSW.flush();
                objXSW.close();
                return true;
            } catch (XMLStreamException ex) {
                Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            } catch (FileNotFoundException ex) {
                Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            } catch (SQLException ex) {
                Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            } catch (Exception ex) {
                Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            }
        }

    }

    private int insertProduct(int intNewsId, String strSubmitUser) {
        //Load data from DB to XML
        Connection objCon = createDBConnection();
        if (objCon == null) {
            //cannot create db connection
            return -1;
        } else {
            try {
                PreparedStatement objPstm = objCon.prepareStatement(strQueryInsertProduct);
                objPstm.setInt(1, intNewsId);
                objPstm.setString(2, strSubmitUser);
               
                return objPstm.executeUpdate();
            } catch (SQLException ex) {
                Logger.getLogger(Controller.class.getName()).log(Level.SEVERE, null, ex);
                return -1;
            }
        }
    }
}
